CREATE TABLE [dbo].[tb_Carreras]
(
[idCarrera] [int] NOT NULL IDENTITY(1, 1),
[Descripcion] [varchar] (90) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[Planid] [int] NOT NULL,
[Mnemo] [varchar] (7) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[Titulo] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Duracion] [int] NULL,
[RegimenPromocion] [int] NULL,
[FechaAlta] [datetime] NOT NULL,
[Baja] [varchar] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[FechaBaja] [datetime] NULL,
[SubOrganizacion] [int] NULL
) ON [PRIMARY]
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
CREATE TRIGGER [dbo].[BajasLogicastb_Carreras] ON [dbo].[tb_Carreras]
FOR UPDATE AS
if update(baja) and (Select baja from inserted)='S' and (Select FechaBaja from inserted) is null BEGIN
	UPDATE tb_Carreras set FechaBaja = getdate() where idCarrera=(Select idCarrera from inserted)
END
else
if (update(baja) and (Select baja from inserted)='N')	UPDATE tb_Carreras set FechaBaja= null where idCarrera=(Select idCarrera from inserted)
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
/*
	GENERA UN GRUPO DE CARRERAS CADA VEZ QUE SE INSERTA UNA NUEVA CARRERA
*/

CREATE TRIGGER [dbo].[insertCarrera] ON [dbo].[tb_Carreras] 
FOR INSERT 
AS
declare @Mnemo varchar(7)
declare  @descripcion varchar(50)
Select @Mnemo =  Mnemo, @descripcion = Descripcion from inserted

if exists (Select * from tb_GrupoDeCarreras where mnemo = @mnemo) 
	begin
	RAISERROR(50025, 16, 1)
	rollback
	end
else
	insert into tb_GrupoDeCarreras  (Mnemo, descripcion)
	values (@Mnemo, @descripcion)
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
CREATE TRIGGER [dbo].[updatetb_Carreras] ON [dbo].[tb_Carreras]
 FOR UPDATE AS 
 IF (update(idCarrera)) begin 
	 SELECT * FROM inserted 
	 IF @@ROWCOUNT = 1 begin 
		 UPDATE tb_RelGrupoCarrera_Carrera
		 SET idCarrera = (SELECT idCarrera FROM inserted)
		 WHERE tb_RelGrupoCarrera_Carrera.idCarrera = (SELECT idCarrera FROM deleted)
	end  ELSE begin 
		 ROLLBACK TRANSACTION 
 end 
 end
GO
ALTER TABLE [dbo].[tb_Carreras] ADD CONSTRAINT [PK_tb_Carreras] PRIMARY KEY NONCLUSTERED  ([idCarrera]) ON [PRIMARY]
GO
ALTER TABLE [dbo].[tb_Carreras] WITH NOCHECK ADD CONSTRAINT [FK_tb_Carreras_tb_PlanesEstudio] FOREIGN KEY ([Planid]) REFERENCES [dbo].[tb_PlanesEstudio] ([idPlanEstudio])
GO
ALTER TABLE [dbo].[tb_Carreras] WITH NOCHECK ADD CONSTRAINT [FK_tb_Carreras_tb_RegimenPromocion2] FOREIGN KEY ([RegimenPromocion]) REFERENCES [dbo].[tb_RegimenPromocion] ([idRegimen])
GO
ALTER TABLE [dbo].[tb_Carreras] WITH NOCHECK ADD CONSTRAINT [FK_tb_Carreras_tb_SubOrganizaciones] FOREIGN KEY ([SubOrganizacion]) REFERENCES [dbo].[tb_SubOrganizaciones] ([idSubOrganizacion])
GO
EXEC sp_bindrule N'[dbo].[S-N]', N'[dbo].[tb_Carreras].[Baja]'
GO
